package cn.edu.xmu.javaee.productdemomybatis.mapper.manual;

import cn.edu.xmu.javaee.productdemomybatis.mapper.manual.po.ProductAllPo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import java.util.List;

@Mapper
public interface ProductSQLMapper extends ProductMapper{
    @Select({
            "select",
            "p.id, p.name, p.original_price, p.weight, p.barcode, p.unit, ",
            "p.origin_place, p.commission_ratio, p.free_threshold, p.status, p.creator, ",
            "p.modifier, p.gmt_create, p.gmt_modified, ",
            "o.id as s_id, o.product_id as s_product_id,o.price as s_price, o.begin_time as s_begin_time, o.end_time as s_end_time, o.quantity as s_quantity, o.max_quantity as s_max_quantity, ",
            "o.creator as s_creator, ",
            "o.modifier as s_modifier, ",
            "o.gmt_create as s_gmt_create, o.gmt_modified as s_gmt_modified, ",
            "op.id as o_id, op.name as o_name, op.original_price as o_original_price, op.weight as o_weight, op.barcode as o_barcode, ",
            "op.unit as o_unit, op.origin_place as o_origin_place, ",
            "op.creator as o_creator,  ",
            "op.modifier as o_modifier, op.gmt_create as o_gmt_create, ",
            "op.gmt_modified as o_gmt_modified ",
            "from goods_product p ",
            "left join goods_onsale o on p.id = o.product_id ",
            "left join goods_goods g on p.id = g.product_id ",
            "left join goods_product op on g.relate_product_id = op.id ",
            "where o.begin_time <= NOW() and o.end_time > NOW() and p.name = #{name}"
        })
        @Results(id = "FullProductMap", value = {
            // Product columns mapping
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="original_price", property="originalPrice", jdbcType=JdbcType.INTEGER),
            @Result(column="weight", property="weight", jdbcType=JdbcType.INTEGER),
            @Result(column="barcode", property="barcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="unit", property="unit", jdbcType=JdbcType.VARCHAR),
            @Result(column="origin_place", property="originPlace", jdbcType=JdbcType.VARCHAR),
            @Result(column="commission_ratio", property="commissionRatio", jdbcType=JdbcType.INTEGER),
            @Result(column="free_threshold", property="freeThreshold", jdbcType=JdbcType.INTEGER),
            @Result(column="status", property="status", jdbcType=JdbcType.SMALLINT),
            @Result(column="creator", property="creator", jdbcType=JdbcType.VARCHAR),
            @Result(column="modifier", property="modifier", jdbcType=JdbcType.VARCHAR),
            @Result(column="gmt_create", property="gmtCreate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="gmt_modified", property="gmtModified", jdbcType=JdbcType.TIMESTAMP),
            @Result(property = "onSaleList", many = @Many(resultMap = "cn.edu.xmu.javaee.productdemomybatis.mapper.manual.ProductMyBatisMapper.OnSaleMap", columnPrefix = "s_")),
            @Result(property = "otherProduct", many = @Many(resultMap = "cn.edu.xmu.javaee.productdemomybatis.mapper.manual.ProductMyBatisMapper.ProductMap", columnPrefix = "o_"))
    })
    List<ProductAllPo> getProductByName(String name);

    @Select({
            "select",
            "p.id, p.name, p.original_price, p.weight, p.barcode, p.unit, ",
            "p.origin_place, p.commission_ratio, p.free_threshold, p.status, p.creator, ",
            "p.modifier, p.gmt_create, p.gmt_modified, ",
            "o.id as s_id, o.product_id as s_product_id,o.price as s_price, o.begin_time as s_begin_time, o.end_time as s_end_time, o.quantity as s_quantity, o.max_quantity as s_max_quantity, ",
            "o.creator as s_creator, ",
            "o.modifier as s_modifier, ",
            "o.gmt_create as s_gmt_create, o.gmt_modified as s_gmt_modified, ",
            "op.id as o_id, op.name as o_name, op.original_price as o_original_price, op.weight as o_weight, op.barcode as o_barcode, ",
            "op.unit as o_unit, op.origin_place as o_origin_place,",
            "op.creator as o_creator, ",
            "op.modifier as o_modifier, op.gmt_create as o_gmt_create, ",
            "op.gmt_modified as o_gmt_modified ",
            "from goods_product p ",
            "left join goods_onsale o on p.id = o.product_id ",
            "left join goods_goods g on p.id = g.product_id ",
            "left join goods_product op on g.relate_product_id = op.id ",
            "where o.begin_time <= NOW() and o.end_time > NOW() and p.name = #{name} and p.shop_id= #{shopId}"
    })
    @ResultMap("FullProductMap")
    List<ProductAllPo> getProductByShopIdAndName(Long shopId, String name);


    @Select({
            "select",
            "p.id, p.name, p.original_price, p.weight, p.barcode, p.unit, ",
            "p.origin_place, p.commission_ratio, p.free_threshold, p.status, p.creator, ",
            "p.modifier, p.gmt_create, p.gmt_modified, ",
            "o.id as s_id, o.product_id as s_product_id,o.price as s_price, o.begin_time as s_begin_time, o.end_time as s_end_time, o.quantity as s_quantity, o.max_quantity as s_max_quantity, ",
            "o.creator as s_creator, ",
            "o.modifier as s_modifier, ",
            "o.gmt_create as s_gmt_create, o.gmt_modified as s_gmt_modified, ",
            "op.id as o_id, op.name as o_name, op.original_price as o_original_price, op.weight as o_weight, op.barcode as o_barcode, ",
            "op.unit as o_unit, op.origin_place as o_origin_place, op.creator as o_creator, ",
            "op.creator as o_creator,",
            "op.modifier as o_modifier, op.gmt_create as o_gmt_create, ",
            "op.gmt_modified as o_gmt_modified ",
            "from goods_product p ",
            "left join goods_onsale o on p.id = o.product_id ",
            "left join goods_goods g on p.id = g.product_id ",
            "left join goods_product op on g.relate_product_id = op.id ",
            "where o.begin_time <= NOW() and o.end_time > NOW() and p.id = #{id}"

    })
    @ResultMap("FullProductMap")
    ProductAllPo getProductById(Integer id);
}
